Imports PTT.CarRes.Data

Public Class ClassQueryInvReserveInfo
    'Public Function CheckApproveTime(ByVal da As String, ByVal StrConn As String) As Boolean
    '    Dim i As Integer = 0
    '    Dim sql As String = ""
    '    Dim ds As New DataSet
    '    Dim ret As Boolean = False
    '    Dim ClsInvReserveInfo As New ClassInvReserveInfo
    '    Dim Query As New PTT.CarRes.Data.QueryInvReserveInfos
    '    Dim Con As OleDb.OleDbConnection
    '    Con = ClsInvReserveInfo.ConnectionOpen(StrConn)
    '    'sql = " select  * from InvReserveInfo"
    '    'sql &= " where ((ApproveStatus=''  and ApproveBdStatus='') or (ApproveStatus<>'' and ApproveBdStatus='')) and "
    '    'sql &= "  convert(int,Replace(substring(StartDate,0,11),'-',''))-2 < '" & da & "'"
    '    '**********
    '    'SELECT f.* FROM   InvReserveInfo f CROSS JOIN InvApproveTime t
    '    ' where ((f.ApproveStatus=''  and f.ApproveBdStatus='') or (f.ApproveStatus<>''  and f.ApproveBdStatus='')) 
    '    'and   ((convert(int,Replace(substring(f.StartDate,0,11),'-',''))-1 = '20060531' 
    '    'and  Convert(float,substring(f.StartDate,12,5))<Convert(float,substring(t.ApproveTime,1,5)))  
    '    'or convert(int,Replace(substring(f.StartDate,0,11),'-',''))-1 < '20060531' )
    '    'and replace(substring(f.StartDate,9,2),'-','')<>'01'
    '    '**********
    '    Dim st1 As String = Now.Hour
    '    Dim st2 As String = Now.Minute
    '    st1 = st1 + "." + st2

    '    '/
    '    sql = " SELECT f.* FROM   InvReserveInfo f CROSS JOIN InvApproveTime t"
    '    sql &= " where ((f.ApproveStatus=''  and f.ApproveBdStatus='') or (f.ApproveStatus<>''  and f.ApproveBdStatus='')) "
    '    sql &= " and   ((dateadd(day,-1,(convert(datetime,substring(f.StartDate,0,11))))= convert(datetime,'" & da & "') "
    '    sql &= " and  Convert(float,'" & st1 & "')>Convert(float,substring(t.ApproveTime,1,5)))  "
    '    sql &= " or dateadd(day,-1,(convert(datetime,substring(f.StartDate,0,11))))< convert(datetime,'" & da & "'))"
    '    'sql &= "  SELECT f.*"
    '    'sql &= " FROM   InvReserveInfo f CROSS JOIN InvApproveTime t"
    '    'sql &= " where ((f.ApproveStatus=''  and f.ApproveBdStatus='') or (f.ApproveStatus<>'' "
    '    'sql &= " and f.ApproveBdStatus='')) and   (convert(int,Replace(substring(f.StartDate,0,11),'-',''))-2 < '" & da & "' and "
    '    'sql &= " Convert(float,substring(f.StartDate,12,5))<Convert(float,substring(t.ApproveTime,1,5)))"
    '    '/

    '    ds = ClsInvReserveInfo.BindData(Con, , , sql)

    '    Dim Conn As OleDb.OleDbConnection
    '    Conn = ClsInvReserveInfo.ConnectionOpen(StrConn)
    '    Dim VTran As OleDb.OleDbTransaction
    '    VTran = ClsInvReserveInfo.BeginTran(Conn)
    '    If Not ds Is Nothing Then
    '        ret = True
    '        With ClsInvReserveInfo
    '            For i = 0 To ds.Tables(0).Rows.Count - 1
    '                If ret = True Then
    '                    .ReserveId = ds.Tables(0).Rows(i)(0)
    '                    .Load(Con)
    '                    .UserStatus = "TO"
    '                    .ApproveStatus = "TO"
    '                    .ApproveBdStatus = "TO"
    '                    If .USP_Update(Conn, VTran) = False Then
    '                        ret = False
    '                        Return ret
    '                        Exit For
    '                    Else
    '                        ret = True
    '                    End If
    '                End If
    '            Next
    '        End With
    '        If ret = True Then
    '            ClsInvReserveInfo.CommitTran(VTran)
    '        Else
    '            ClsInvReserveInfo.RollBackTran(VTran)
    '        End If
    '    Else
    '        ret = True
    '    End If
    '    ClsInvReserveInfo.ConnectionClose(Con)
    '    ClsInvReserveInfo.ConnectionClose(Conn)

    '    Return ret
    'End Function

    Public Function CheckApproveTime_ApproveBd(ByVal da As String, ByVal StrConn As String) As Boolean
        Dim i As Integer = 0
        Dim sql As String = ""
        Dim ds As New DataSet
        Dim ret As Boolean = False
        Dim ClsInvReserveInfo As New ClassInvReserveInfo
        Dim Query As New PTT.CarRes.Data.QueryInvReserveInfos
        Dim Con As OleDb.OleDbConnection
        Con = ClsInvReserveInfo.ConnectionOpen(StrConn)
        '**********
        Dim st1 As String = Now.Hour
        Dim st2 As String = Now.Minute
        st1 = st1 + "." + st2

        '/
        sql = " SELECT f.* FROM   InvReserveInfo f CROSS JOIN InvApproveTime t"
        sql &= " where (f.ApproveStatus='' ) "
        sql &= " and   (((dateadd(day,-1,(convert(datetime,substring(f.StartDate,0,11))))= convert(datetime,'" & da & "') "
        sql &= " and  Convert(float,'" & st1 & "')>Convert(float,substring(t.ApproveTime,1,5)))  "
        sql &= " or dateadd(day,-1,(convert(datetime,substring(f.StartDate,0,11))))< convert(datetime,'" & da & "')"
        sql &= "  and f.agentid=''))"


        ds = ClsInvReserveInfo.BindData(Con, , , sql)

        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvReserveInfo.ConnectionOpen(StrConn)
        Dim VTran As OleDb.OleDbTransaction
        VTran = ClsInvReserveInfo.BeginTran(Conn)
        If Not ds Is Nothing Then
            ret = True
            With ClsInvReserveInfo
                For i = 0 To ds.Tables(0).Rows.Count - 1
                    If ret = True Then
                        .ReserveId = ds.Tables(0).Rows(i)(0)
                        .Load(Con)
                        .UserStatus = "TO"
                        .ApproveStatus = "TO"
                        .ApproveBdStatus = "TO"
                        If .USP_Update(Conn, VTran) = False Then
                            ret = False
                            Return ret
                            Exit For
                        Else
                            ret = True
                        End If
                    End If
                Next
            End With
            If ret = True Then
                ClsInvReserveInfo.CommitTran(VTran)
            Else
                ClsInvReserveInfo.RollBackTran(VTran)
            End If
        Else
            ret = True
        End If
        ClsInvReserveInfo.ConnectionClose(Con)
        ClsInvReserveInfo.ConnectionClose(Conn)

        Return ret
    End Function
    Public Function Add(ByVal InvReserveInfo As QueryInvReserveInfo, ByVal InvDataTableHuman As DataSet, ByVal InvDataTableReserveDataTime As DataSet, ByVal StrConn As String) As Boolean
        Dim ClsInvReserveInfo As New ClassInvReserveInfo
        Dim ClsInvReserveDataTime As New ClassInvReserveDataTime
        Dim ClsInvHuman As New ClassInvHuman
        Dim ii As Integer
        Dim xCount As Integer
        Dim Conn As OleDb.OleDbConnection
        Dim VTran As OleDb.OleDbTransaction
        Conn = ClsInvReserveInfo.ConnectionOpen(StrConn)
        VTran = ClsInvReserveInfo.BeginTran(Conn)
        Dim ret As Boolean = False

        With ClsInvReserveInfo
            .ReserveId = InvReserveInfo.ReserveId
            .ReserveDate = InvReserveInfo.ReserveDate
            .UserId = InvReserveInfo.UserId
            .UnitId = InvReserveInfo.UnitId
            .AgentId = InvReserveInfo.AgentId
            .Telephone = InvReserveInfo.Telephone
            .CarAmount = InvReserveInfo.CarAmount
            .StartDate = InvReserveInfo.StartDate
            .StopDate = InvReserveInfo.StopDate
            .StatusDriver = InvReserveInfo.StatusDriver
            .HumanAmount = InvReserveInfo.HumanAmount
            .Area = InvReserveInfo.Area
            .Remark = InvReserveInfo.Remark
            .Description = InvReserveInfo.Description
            .UserStatus = InvReserveInfo.UserStatus
            .ReserveCost = InvReserveInfo.ReserveCost
            .ApproveId = InvReserveInfo.ApproveId
            .ApproveStatus = InvReserveInfo.ApproveStatus
            .ApprovedDate = InvReserveInfo.ApprovedDate
            .ApproveRemark = InvReserveInfo.ApproveRemark
            .ApproveBdStatus = InvReserveInfo.ApproveBdStatus
            .ApproveBdDate = InvReserveInfo.ApproveBdDate
            .ApproveBdRemark = InvReserveInfo.ApproveBdRemark
            .FlageReserve = InvReserveInfo.FlageReserve
            .ApproveBdId = InvReserveInfo.ApproveBdId
            .WareaGroupId = InvReserveInfo.WareaGroupId
            .DataTypeId = InvReserveInfo.DataTypeId
            .TypeId = InvReserveInfo.TypeId
            .PersonNo = InvReserveInfo.PersonNo
            ret = .USP_Save(Conn, VTran)
        End With
        If ret = True Then
            xCount = InvDataTableReserveDataTime.Tables(0).Rows.Count - 1
            For ii = 0 To xCount
                With ClsInvReserveDataTime
                    .DayId = CInt(InvDataTableReserveDataTime.Tables(0).Rows(ii).Item("DayId"))
                    .ReserveDay = CStr(InvDataTableReserveDataTime.Tables(0).Rows(ii).Item("ReserveDay"))
                    .StartTime = CStr(InvDataTableReserveDataTime.Tables(0).Rows(ii).Item("StartTime"))
                    .StopTime = CStr(InvDataTableReserveDataTime.Tables(0).Rows(ii).Item("StopTime"))
                    .ReserveId = CStr(InvDataTableReserveDataTime.Tables(0).Rows(ii).Item("ReserveId"))
                    ret = .USP_Save(Conn, VTran)
                    If ret = False Then
                        Exit For
                    End If
                End With
            Next
            If ret = True Then
                xCount = InvDataTableHuman.Tables(0).Rows.Count - 1
                For ii = 0 To xCount
                    With ClsInvHuman
                        .HumanId = CInt(InvDataTableHuman.Tables(0).Rows(ii).Item("HumanId"))
                        .Name = CStr(InvDataTableHuman.Tables(0).Rows(ii).Item("Name"))
                        .ReserveId = CStr(InvDataTableHuman.Tables(0).Rows(ii).Item("ReserveId"))
                        ret = .USP_Save(Conn, VTran)
                        If ret = False Then
                            Exit For
                        End If
                    End With
                Next
                If ret = True Then
                    ClsInvReserveInfo.CommitTran(VTran)
                    ret = True
                Else
                    ClsInvReserveInfo.RollBackTran(VTran)
                    ret = False
                End If

            Else
                ClsInvReserveInfo.RollBackTran(VTran)
                ret = False
            End If

        Else
            ClsInvReserveInfo.RollBackTran(VTran)
            ret = False
        End If


        ClsInvReserveInfo.ConnectionClose(Conn)
        Return ret
    End Function

    Public Function Edit(ByVal InvReserveInfo As QueryInvReserveInfo, ByVal StrConn As String) As Boolean
        Dim ClsInvReserveInfo As New ClassInvReserveInfo
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvReserveInfo.ConnectionOpen(StrConn)
        Dim ret As Boolean = False
        With ClsInvReserveInfo
            .ReserveId = InvReserveInfo.ReserveId
            .Load(Conn)
            .ReserveDate = InvReserveInfo.ReserveDate
            .UserId = InvReserveInfo.UserId
            .UnitId = InvReserveInfo.UnitId
            .AgentId = InvReserveInfo.AgentId
            .Telephone = InvReserveInfo.Telephone
            .CarAmount = InvReserveInfo.CarAmount
            .StartDate = InvReserveInfo.StartDate
            .StopDate = InvReserveInfo.StopDate
            .StatusDriver = InvReserveInfo.StatusDriver
            .HumanAmount = InvReserveInfo.HumanAmount
            .Area = InvReserveInfo.Area
            .Remark = InvReserveInfo.Remark
            .Description = InvReserveInfo.Description
            .UserStatus = InvReserveInfo.UserStatus
            .ReserveCost = InvReserveInfo.ReserveCost
            .ApproveId = InvReserveInfo.ApproveId
            .ApproveStatus = InvReserveInfo.ApproveStatus
            .ApprovedDate = InvReserveInfo.ApprovedDate
            .ApproveRemark = InvReserveInfo.ApproveRemark
            .ApproveBdStatus = InvReserveInfo.ApproveBdStatus
            .ApproveBdDate = InvReserveInfo.ApproveBdDate
            .ApproveBdRemark = InvReserveInfo.ApproveBdRemark
            .FlageReserve = InvReserveInfo.FlageReserve
            .ApproveBdId = InvReserveInfo.ApproveBdId
            .WareaGroupId = InvReserveInfo.WareaGroupId
            .DataTypeId = InvReserveInfo.DataTypeId
            .TypeId = InvReserveInfo.TypeId
            .PersonNo = InvReserveInfo.PersonNo
            ret = .USP_Update(Conn)
        End With
        ClsInvReserveInfo.ConnectionClose(Conn)
        Return ret
    End Function

    'Public Function Edit_ApproveBd(ByVal ReserveId As String, ByVal WareaGroup As String, ByVal StrConn As String) As Boolean
    '    Dim ClsInvReserveInfo As New ClassInvReserveInfo
    '    Dim Conn As OleDb.OleDbConnection
    '    Conn = ClsInvReserveInfo.ConnectionOpen(StrConn)
    '    Dim str As String
    '    Dim ret As Boolean = False
    '    str = "Update  InvReserveInfo set WareaGroupId='" & WareaGroup & "' where ReserveId='" & ReserveId & "'"

    '    ClsInvReserveInfo.ConnectionClose(Conn)
    '    Return ret
    'End Function

    Public Function Delete(ByVal InvReserveInfo As QueryInvReserveInfo, ByVal StrConn As String) As Boolean
        Dim ClsInvReserveInfo As New ClassInvReserveInfo
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvReserveInfo.ConnectionOpen(StrConn)
        Dim ret As Boolean = False
        With ClsInvReserveInfo
            .ReserveId = InvReserveInfo.ReserveId
            .Load(Conn)
            ret = .USP_Delete(Conn)
        End With
        ClsInvReserveInfo.ConnectionClose(Conn)
        Return ret
    End Function

    Public Function Search(Optional ByVal ParaReserveId As String = "", _
    Optional ByVal ParaReserveDate As String = "", _
    Optional ByVal ResserveStatus As String = "", _
    Optional ByVal UnitCode As String = "", _
    Optional ByVal UserCode As String = "", _
    Optional ByVal ApproveCode As String = "", _
    Optional ByVal WareaGroupId As String = "", _
    Optional ByVal AgentId As String = "", _
    Optional ByVal StrConn As String = "", _
    Optional ByVal Start_Date As String = "", _
    Optional ByVal Stop_Date As String = "") As QueryInvReserveInfos
        Dim ClsInvReserveInfo As New ClassInvReserveInfo
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvReserveInfo.ConnectionOpen(StrConn)
        Dim ret As QueryInvReserveInfos = Nothing
        Dim str As String = ""
        Dim ch As String = ""
        Dim Condition As String = ""
        str &= " SELECT  *  FROM  InvReserveInfo"
        If ParaReserveId & "" <> "" Then
            If Condition & "" <> "" Then
                Condition &= " and "
            End If
            Condition &= "ReserveId like '%" & ParaReserveId & "%'"
        End If


        If ParaReserveDate & "" <> "" Then
            If Condition & "" <> "" Then
                Condition &= " and "
            End If
            Condition &= "ReserveDate like '%" & ParaReserveDate & "%'"
        End If

        If ResserveStatus & "" <> "" Then
            If Condition & "" <> "" Then
                Condition &= " and "
            End If
            Condition &= "UserStatus = '" & ResserveStatus & "'"
        End If

        If UnitCode & "" <> "" Then
            If Condition & "" <> "" Then
                Condition &= " and "
            End If
            If Right(UnitCode.Replace("'", ""), 7) = "0000000" Then
                Condition &= "  UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 1) & "%' "
            ElseIf Right(UnitCode.Replace("'", ""), 6) = "000000" Then
                Condition &= "  UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 2) & "%' "
            ElseIf Right(UnitCode.Replace("'", ""), 4) = "0000" Then
                Condition &= "    UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 4) & "%' "
            ElseIf Right(UnitCode.Replace("'", ""), 3) = "000" Then
                Condition &= "  UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 5) & "%' "
            Else
                Condition &= "   UnitId = '" & UnitCode & "' "
            End If
            'Condition &= "(UnitId = " & UnitCode & ")"

        End If

        If UserCode & "" <> "" Then
            ch = "1"
            If Condition & "" <> "" Then
                Condition &= " and "
            End If
            Condition &= " (UserId = '" & UserCode & "'"
            If AgentId & "" <> "" Then
                If Condition & "" <> "" Then
                    Condition &= " or "
                End If
                Condition &= " AgentId = '" & AgentId & "')"
            Else
                Condition &= " )"
            End If
        Else
            ch = ""
        End If
        If ch = "" Then
            If AgentId & "" <> "" Then
                If Condition & "" <> "" Then
                    Condition &= " and"
                End If
                Condition &= " AgentId = '" & AgentId & "'"
            End If
        End If

        If ApproveCode & "" <> "" Then
            If Condition & "" <> "" Then
                Condition &= " and "
            End If
            Condition &= " ApproveId = '" & ApproveCode & "'"
        End If

        If WareaGroupId & "" <> "" Then
            If Condition & "" <> "" Then
                Condition &= " and "
            End If
            Condition &= "WareaGroupId = '" & WareaGroupId & "'"
        End If

        If Start_Date & "" <> "" And Stop_Date & "" <> "" Then
            If Condition & "" <> "" Then
                Condition &= " and "
            End If
            Condition &= "(Substring(StartDate,1,10) >= '" & Start_Date & "' AND Substring(StartDate,1,10) <= '" & Stop_Date & "')"
        End If



        If Condition & "" <> "" Then
            str &= " where  "
            str &= Condition
        End If

        'str &= " Order By ReserveId desc "
        str &= " Order by StartDate asc"

        Dim ds As DataSet
        With ClsInvReserveInfo
            ds = .BindData(Conn, , , str)
            If Not ds Is Nothing Then
                Dim i As Integer
                Dim results As New QueryInvReserveInfos
                Dim RReserveCost As Decimal = -1

                Try
                    For i = 0 To ds.Tables(0).Rows.Count - 1
                        With ds.Tables(0).Rows(i)
                            If IsNumeric(.Item("ReserveCost")) Then
                                RReserveCost = .Item("ReserveCost")
                            Else
                                RReserveCost = -1
                            End If

                            results.Add(New QueryInvReserveInfo( _
                              .Item("ReserveId") & "", _
                              .Item("ReserveDate") & "", _
                              .Item("UserId") & "", _
                              .Item("UnitId") & "", _
                              .Item("AgentId") & "", _
                              .Item("Telephone") & "", _
                              .Item("CarAmount") & "", _
                              .Item("StartDate") & "", _
                              .Item("StopDate") & "", _
                              .Item("StatusDriver") & "", _
                              .Item("HumanAmount") & "", _
                              .Item("Area") & "", _
                              .Item("Remark") & "", _
                              .Item("Description") & "", _
                              .Item("UserStatus") & "", _
                             .Item("ReserveCost") & "", _
                              .Item("ApproveId") & "", _
                              .Item("ApproveStatus") & "", _
                              .Item("ApprovedDate") & "", _
                              .Item("ApproveRemark") & "", _
                              .Item("ApproveBdStatus") & "", _
                              .Item("ApproveBdDate") & "", _
                              .Item("ApproveBdRemark") & "", _
                              .Item("FlageReserve") & "", _
                              .Item("ApproveBdId") & "", _
                              .Item("WareaGroupId") & "", _
                              .Item("DataTypeId") & "", _
                              .Item("TypeId") & "", _
                              .Item("PersonNo") & "" _
                            ))
                        End With
                    Next
                    ret = results
                Catch ex As Exception
                    Return Nothing
                    'Throw New DALException(ex.Message)
                End Try
            End If
        End With
        ClsInvReserveInfo.ConnectionClose(Conn)
        Return ret
    End Function
    Public Function SearchCloseWork(Optional ByVal ParaReserveId As String = "", _
    Optional ByVal ParaReserveDate As String = "", _
    Optional ByVal ResserveStatus As String = "", _
    Optional ByVal UnitCode As String = "", _
    Optional ByVal UserCode As String = "", _
    Optional ByVal ApproveCode As String = "", _
    Optional ByVal StrConn As String = "") As QueryInvReserveInfos
        Dim ClsInvReserveInfo As New ClassInvReserveInfo
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvReserveInfo.ConnectionOpen(StrConn)
        Dim ret As QueryInvReserveInfos = Nothing
        Dim str As String = ""
        Dim Condition As String = ""
        str &= " SELECT DISTINCT InvReserveInfo.* "
        str &= " FROM         InvReserveInfo INNER JOIN"
        str &= " InvReserveDataCarDriver ON InvReserveInfo.ReserveId = InvReserveDataCarDriver.ReserveId "
        If UnitCode <> "" Then
            If Right(UnitCode.Replace("'", ""), 7) = "0000000" Then
                Condition &= " and (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 1) & "%' )"
            ElseIf Right(UnitCode.Replace("'", ""), 6) = "000000" Then
                Condition &= " and  (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 2) & "%' )"
            ElseIf Right(UnitCode.Replace("'", ""), 4) = "0000" Then
                Condition &= "  and  (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 4) & "%' )"
            ElseIf Right(UnitCode.Replace("'", ""), 3) = "000" Then
                Condition &= " and  (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 5) & "%' )"
            Else
                Condition &= "  and  (InvReserveInfo.UnitId = " & UnitCode & " )"
            End If
        End If
        If Condition & "" <> "" Then
            str &= " where  (InvReserveDataCarDriver.CostWorkStatus = 0) "
            str &= Condition
        End If
        Dim ds As DataSet
        With ClsInvReserveInfo
            ds = .BindData(Conn, , , str)
            If Not ds Is Nothing Then
                Dim i As Integer
                Dim results As New QueryInvReserveInfos
                Dim RReserveCost As Decimal = -1

                Try
                    For i = 0 To ds.Tables(0).Rows.Count - 1
                        With ds.Tables(0).Rows(i)
                            If IsNumeric(.Item("ReserveCost")) Then
                                RReserveCost = .Item("ReserveCost")
                            Else
                                RReserveCost = -1
                            End If

                            results.Add(New QueryInvReserveInfo( _
                              .Item("ReserveId") & "", _
                              .Item("ReserveDate") & "", _
                              .Item("UserId") & "", _
                              .Item("UnitId") & "", _
                              .Item("AgentId") & "", _
                              .Item("Telephone") & "", _
                              .Item("CarAmount") & "", _
                              .Item("StartDate") & "", _
                              .Item("StopDate") & "", _
                              .Item("StatusDriver") & "", _
                              .Item("HumanAmount") & "", _
                              .Item("Area") & "", _
                              .Item("Remark") & "", _
                              .Item("Description") & "", _
                              .Item("UserStatus") & "", _
                             .Item("ReserveCost") & "", _
                              .Item("ApproveId") & "", _
                              .Item("ApproveStatus") & "", _
                              .Item("ApprovedDate") & "", _
                              .Item("ApproveRemark") & "", _
                              .Item("ApproveBdStatus") & "", _
                              .Item("ApproveBdDate") & "", _
                              .Item("ApproveBdRemark") & "", _
                              .Item("FlageReserve") & "", _
                              .Item("ApproveBdId") & "", _
                              .Item("WareaGroupId") & "", _
                              .Item("DataTypeId") & "", _
                              .Item("TypeId") & "", _
                              .Item("PersonNo") & "" _
                            ))
                        End With
                    Next
                    ret = results
                Catch ex As Exception
                    Return Nothing
                    'Throw New DALException(ex.Message)
                End Try
            End If
        End With
        ClsInvReserveInfo.ConnectionClose(Conn)
        Return ret
    End Function

    Function ShowDataForCloseWork(Optional ByVal ParaReserveId As String = "", _
    Optional ByVal ParaReserveDate As String = "", _
    Optional ByVal ResserveStatus As String = "", _
    Optional ByVal UnitCode As String = "", _
    Optional ByVal UserCode As String = "", _
    Optional ByVal ApproveCode As String = "", _
    Optional ByVal WareaGroup As String = "", _
    Optional ByVal StrConn As String = "", _
    Optional ByVal Start_Date As String = "", _
    Optional ByVal Stop_Date As String = "", _
    Optional ByVal BD_NUMBER As String = "") As DataSet

        Dim ClsInvReserveInfo As New ClassInvReserveInfo
        Dim Conn As OleDb.OleDbConnection
        Dim sTrSql As String = ""
        Dim Condition As String = ""
        Conn = ClsInvReserveInfo.ConnectionOpen(StrConn)
        Try
            sTrSql = " SELECT     InvReserveDataCarDriver.BdNumber, InvReserveDataCarDriver.WorkCost, InvReserveInfo.ReserveId, InvReserveInfo.UserId, "
            sTrSql = sTrSql & "   InvReserveInfo.StartDate, InvReserveInfo.StopDate, InvReserveDataCarDriver.CostWorkStatus, InvReserveInfo.Area  , "
            sTrSql = sTrSql & "   InvReserveInfo.UnitId,InvReserveInfo.UserStatus "
            sTrSql = sTrSql & " FROM InvReserveDataCarDriver INNER JOIN"
            sTrSql = sTrSql & "  InvReserveInfo ON InvReserveDataCarDriver.ReserveId = InvReserveInfo.ReserveId"
            sTrSql = sTrSql & "  where  (InvReserveDataCarDriver.CostWorkStatus = 0) "
            If UnitCode <> "" Then
                If Right(UnitCode.Replace("'", ""), 7) = "0000000" Then
                    Condition &= " and (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 1) & "%' )"
                ElseIf Right(UnitCode.Replace("'", ""), 6) = "000000" Then
                    Condition &= " and  (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 2) & "%' )"
                ElseIf Right(UnitCode.Replace("'", ""), 4) = "0000" Then
                    Condition &= "  and  (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 4) & "%' )"
                ElseIf Right(UnitCode.Replace("'", ""), 3) = "000" Then
                    Condition &= " and  (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 5) & "%' )"
                Else
                    Condition &= "  and  (InvReserveInfo.UnitId = " & UnitCode & " )"
                End If
            End If

            If ParaReserveId <> "" And BD_NUMBER <> "" Then
                sTrSql = sTrSql & "  AND  (InvReserveDataCarDriver.ReserveId Like '%" & ParaReserveId & "%' OR InvReserveDataCarDriver.BdNumber Like '%" & BD_NUMBER & "%' ) "
            ElseIf ParaReserveId <> "" And BD_NUMBER = "" Then
                sTrSql = sTrSql & "  AND  (InvReserveDataCarDriver.ReserveId Like '%" & ParaReserveId & "%' )"
            ElseIf ParaReserveId = "" And BD_NUMBER <> "" Then
                sTrSql = sTrSql & "  AND  (InvReserveDataCarDriver.BdNumber Like '%" & BD_NUMBER & "%') "
            End If

            If Start_Date & "" <> "" And Stop_Date & "" <> "" Then
                sTrSql = sTrSql & " AND  ((Substring(InvReserveInfo.StartDate,1,10) >= '" & Start_Date & "') AND (Substring(InvReserveInfo.StartDate,1,10) <= '" & Stop_Date & "'))"
            End If

            If WareaGroup <> "" Then
                sTrSql = sTrSql & " and  (InvReserveInfo.WareaGroupId = '" & WareaGroup & "') "
            End If


            sTrSql = sTrSql & "  " & Condition

            sTrSql = sTrSql & " order by InvReserveInfo.StartDate asc "

            Dim ds As DataSet

            ds = ClsInvReserveInfo.BindData(Conn, , , sTrSql)
            If Not ds Is Nothing Then
                Return ds
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Return Nothing
        End Try

        ClsInvReserveInfo.ConnectionClose(Conn)



    End Function

    Public Function Load(Optional ByVal ParaReserveId As String = "", _
    Optional ByVal ParaApproveBdId As String = "", _
    Optional ByVal ParaWareaGroupId As String = "", _
    Optional ByVal ParaDataTypeId As String = "", Optional ByVal StrConn As String = "") As QueryInvReserveInfos
        Dim ClsInvReserveInfo As New ClassInvReserveInfo
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvReserveInfo.ConnectionOpen(StrConn)
        Dim ret As QueryInvReserveInfos = Nothing
        Dim str As String = ""
        Dim Condition As String = ""
        str &= " Select * from InvReserveInfo"
        If ParaReserveId & "" <> "" Then
            If Condition & "" <> "" Then Condition &= " and "
            Condition &= "(ReserveId = " & ParaReserveId & ")"
        End If
        If ParaApproveBdId & "" <> "" Then
            If Condition & "" <> "" Then Condition &= " and "
            Condition &= "(ApproveBdId = " & ParaApproveBdId & ")"
        End If
        If ParaWareaGroupId & "" <> "" Then
            If Condition & "" <> "" Then Condition &= " and "
            Condition &= "(WareaGroupId = " & ParaWareaGroupId & ")"
        End If
        If ParaDataTypeId & "" <> "" Then
            If Condition & "" <> "" Then Condition &= " and "
            Condition &= "(DataTypeId = " & ParaDataTypeId & ")"
        End If

        If Condition & "" <> "" Then
            str &= " where "
            str &= Condition
        Else
            Return Nothing
            Exit Function
        End If
        Dim ds As DataSet
        With ClsInvReserveInfo
            ds = .BindData(Conn, , , str)
            If Not ds Is Nothing Then
                Dim i As Integer
                Dim results As New QueryInvReserveInfos
                Dim RReserveCost As Decimal = -1

                Try
                    For i = 0 To ds.Tables(0).Rows.Count - 1
                        With ds.Tables(0).Rows(i)
                            If IsNumeric(.Item("ReserveCost")) Then
                                RReserveCost = .Item("ReserveCost")
                            Else
                                RReserveCost = -1
                            End If

                            results.Add(New QueryInvReserveInfo( _
                              .Item("ReserveId") & "", _
                              .Item("ReserveDate") & "", _
                              .Item("UserId") & "", _
                              .Item("UnitId") & "", _
                              .Item("AgentId") & "", _
                              .Item("Telephone") & "", _
                              .Item("CarAmount") & "", _
                              .Item("StartDate") & "", _
                              .Item("StopDate") & "", _
                              .Item("StatusDriver") & "", _
                              .Item("HumanAmount") & "", _
                              .Item("Area") & "", _
                              .Item("Remark") & "", _
                              .Item("Description") & "", _
                              .Item("UserStatus") & "", _
                              .Item("ReserveCost") & "", _
                              .Item("ApproveId") & "", _
                              .Item("ApproveStatus") & "", _
                              .Item("ApprovedDate") & "", _
                              .Item("ApproveRemark") & "", _
                              .Item("ApproveBdStatus") & "", _
                              .Item("ApproveBdDate") & "", _
                              .Item("ApproveBdRemark") & "", _
                              .Item("FlageReserve") & "", _
                              .Item("ApproveBdId") & "", _
                              .Item("WareaGroupId") & "", _
                              .Item("DataTypeId") & "", _
                              .Item("TypeId") & "", _
                              .Item("PersonNo") & "" _
                            ))
                        End With
                    Next
                    ret = results
                Catch ex As Exception
                    Return Nothing
                    'Throw New DALException(ex.Message)
                End Try
            End If
        End With
        ClsInvReserveInfo.ConnectionClose(Conn)
        Return ret
    End Function
End Class